package com.ctcemti.mdm.common.util;

import com.ctcemti.mdm.project.tool.generatecode.entity.GenTable;
import com.ctcemti.mdm.project.tool.generatecode.entity.GenTableColumn;
import com.ctcemti.mdm.project.tool.generatecode.entity.JDBCProperies;
import com.ctcemti.mdm.project.tool.generatecode.entity.GenTable;
import com.ctcemti.mdm.project.tool.generatecode.entity.GenTableColumn;
import com.ctcemti.mdm.project.tool.generatecode.entity.JDBCProperies;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class JdbcUtil {


	public static Connection getConnect(JDBCProperies jdbcProperies) throws SQLException {
		String driver = jdbcProperies.getDriver();
		String username = jdbcProperies.getUsername();
		String password = jdbcProperies.getPassword();
		String url  = jdbcProperies.getJdbcurl();
		Connection connection = null;
		try {
			// 注册
			Class.forName(driver);
			// 连接
			connection = DriverManager.getConnection(url, username, password);
			return connection;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	
	
	/**
	 * 查找获取所有table值
	 * @param connection
	 * @param connection
	 * @return
	 */
	public static List<GenTable> selectTableData(Connection connection, String sql, GenTable entity) throws SQLException {
		List<GenTable> list = new ArrayList<GenTable>();
		try {
			list = new ArrayList<GenTable>();
			PreparedStatement prs = connection.prepareStatement(sql);
			int flag = 1;
            if(!StringUtils.isEmpty(entity.getTableName())){
                prs.setString(1, entity.getTableName());
				flag++;
            }
            if(!StringUtils.isEmpty(entity.getTableComment())){
                prs.setString(flag, entity.getTableComment());
            }

			ResultSet rs = prs.executeQuery();
			ResultSetMetaData md = rs.getMetaData();// 获取键名
			int columnCount = md.getColumnCount();// 获取行的数量

			while (rs.next()) {
				GenTable genTable = new GenTable();
				Map<String, Object> rowData = new HashMap<String, Object>();// 声明Map
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));// 获取键名及值
				}
				ConvertUtils.register(new DateConverter(null), java.util.Date.class);
				BeanUtils.populate(genTable,rowData);
				list.add(genTable);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}finally {
			if(connection!=null){
				connection.close();
			}
		}
		return list;
	}

	/**
	 * 查找获取所有table值
	 * @param connection
	 * @param connection
	 * @return
	 */
	public static List<GenTable> selectTablesByNames(Connection connection,String sql,String[] tableName) throws SQLException {
		List<GenTable> list = new ArrayList<GenTable>();
		try {
			list = new ArrayList<GenTable>();
			PreparedStatement prs = connection.prepareStatement(sql);
			for(int i=0;i<tableName.length;i++){
				prs.setString(i+1, tableName[i]);
			}
			ResultSet rs = prs.executeQuery();
			ResultSetMetaData md = rs.getMetaData();// 获取键名
			int columnCount = md.getColumnCount();// 获取行的数量

			while (rs.next()) {
				GenTable genTable = new GenTable();
				Map<String, Object> rowData = new HashMap<String, Object>();// 声明Map
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));// 获取键名及值
				}
				ConvertUtils.register(new DateConverter(null), java.util.Date.class);
				BeanUtils.populate(genTable,rowData);
				list.add(genTable);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}finally {
			if(connection!=null){
				connection.close();
			}
		}
		return list;
	}

	/**
	 * 查找获取所有table值
	 * @param connection
	 * @param connection
	 * @return
	 */
	public static List<GenTableColumn> selectTableColumnsData(Connection connection, String sql, String tableName) throws SQLException {

		List<GenTableColumn> list = new ArrayList<GenTableColumn>();
		try {
			list = new ArrayList<GenTableColumn>();
			PreparedStatement prs = connection.prepareStatement(sql);
			prs.setString(1,tableName);
			ResultSet rs = prs.executeQuery();
			ResultSetMetaData md = rs.getMetaData();// 获取键名
			int columnCount = md.getColumnCount();// 获取行的数量
			while (rs.next()){
				GenTableColumn genTableColumn = new GenTableColumn();
				Map<String, Object> rowData = new HashMap<String, Object>();// 声明Map
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));// 获取键名及值
				}
				ConvertUtils.register(new DateConverter(null), java.util.Date.class);
				BeanUtils.populate(genTableColumn,rowData);
				list.add(genTableColumn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}finally {
			if(connection!=null){
				connection.close();
			}
		}
		return list;
	}

	public static void main(String[] args) throws SQLException {
		//Connection connection = getConnect("com.microsoft.sqlserver.jdbc.SQLServerDriver","sa","123456","jdbc:sqlserver://localhost;DatabaseName=MDM");

		//selectTableColumnsData(connection,sql,"inf_dict_item");
	}

	public boolean insertData(Connection connection,Map<String, Object> map) {
		boolean flag = false;
		String sql = "INSERT INTO [dbo].[t_d_measure_data]  (station_id,pid,point_id,point_no,current_value,first_value,before_value,span,current_d_value,total_d_value,d_rate,date_state,test_person,test_date,instrument_id,is_valid,sounding_id) VALUES (?, ?, ?, ? ,?, ?,?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement prs = connection.prepareStatement(sql);
			prs.setString(1, String.valueOf(map.get("station_id")));
			prs.setString(1, String.valueOf(map.get("pid")));
			prs.setString(1, String.valueOf(map.get("point_id")));
			prs.setString(1, String.valueOf(map.get("point_no")));
			prs.setString(1, String.valueOf(map.get("current_value")));
			prs.setString(1, String.valueOf(map.get("first_value")));
			prs.setString(1, String.valueOf(map.get("before_value")));
			prs.setString(1, String.valueOf(map.get("span")));
			prs.setString(1, String.valueOf(map.get("current_d_value")));
			prs.setString(1, String.valueOf(map.get("total_d_value")));
			prs.setString(1, String.valueOf(map.get("d_rate")));
			prs.setString(1, String.valueOf(map.get("date_state")));
			prs.setString(1, String.valueOf(map.get("test_person")));
			prs.setString(1, String.valueOf(map.get("test_date")));
			prs.setString(1, String.valueOf(map.get("instrument_id")));
			prs.setString(1, String.valueOf(map.get("is_valid")));
			prs.setString(1, String.valueOf(map.get("sounding_id")));
			flag = prs.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
}
